﻿Public Class frmInThongKeSanLuongTieuThu
    'ReportType 1- ngày; 2- tháng; 3-năm; 4-từ ngày đến ngày
    Public ReportType As Integer
    Public Ngay As String
    Public Thang As String
    Public Nam As String

    Public TuNgay As String
    Public DenNgay As String
    Public sTuNgay As String
    Public sDenNgay As String

    Private Sub frmInThongKeSanLuongTieuThu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
        End Select
    End Sub
    Protected Function Create_GroupingList() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim IDLoai As DataColumn = New DataColumn("IDLoai")
            IDLoai.DataType = System.Type.GetType("System.String")
            IDLoai.DefaultValue = ""
            dtTable.Columns.Add(IDLoai)

            Dim TenLoai As DataColumn = New DataColumn("TenLoai")
            TenLoai.DataType = System.Type.GetType("System.String")
            TenLoai.DefaultValue = ""
            dtTable.Columns.Add(TenLoai)

            Dim MaSanPham As DataColumn = New DataColumn("MaSanPham")
            MaSanPham.DataType = System.Type.GetType("System.String")
            MaSanPham.DefaultValue = ""
            dtTable.Columns.Add(MaSanPham)

            Dim TenSanPham As DataColumn = New DataColumn("TenSanPham")
            TenSanPham.DataType = System.Type.GetType("System.String")
            TenSanPham.DefaultValue = ""
            dtTable.Columns.Add(TenSanPham)

            Dim DonViTinh As DataColumn = New DataColumn("DonViTinh")
            DonViTinh.DataType = System.Type.GetType("System.String")
            DonViTinh.DefaultValue = ""
            dtTable.Columns.Add(DonViTinh)

            Dim QuyCach As DataColumn = New DataColumn("QuyCach")
            QuyCach.DataType = System.Type.GetType("System.String")
            QuyCach.DefaultValue = ""
            dtTable.Columns.Add(QuyCach)

            Dim SoLuong As DataColumn = New DataColumn("SoLuong")
            SoLuong.DataType = System.Type.GetType("System.String")
            SoLuong.DefaultValue = ""
            dtTable.Columns.Add(SoLuong)

            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
#Region "Thống kê - báo cáo"
    '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
    Private Function TinhSanLuongTieuThu(ByVal IDSanPham As Integer) As Single
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select SoLuong From ChiTietDonHangBan Where LoaiPhi='HangHoa' And IdSanPham={0} ", IDSanPham)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + Single.Parse((dtThanhTien.Rows(i)("SoLuong")))
            Next
            Return sRet
        Catch ex As Exception
            MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
        End Try
    End Function
#End Region
    Sub Report_TheoNgay()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim NgayBan As String = dtpTheoNgay.Value.ToShortDateString
            Dim query As String = "" '"Select * From DonHangBan Order by Ngay_So DESC" REM Where NgayBan='" & NgayBan & "'" & " Order by Ngay_So DESC"
            REM Grouping list
            Dim groupingList As New DataTable
            groupingList = Create_GroupingList()
            'title
            Dim dtLoaiSanPhamTieuThu As New DataTable
            query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
                   " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
                    " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay='" & Ngay & "' )"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiSanPhamTieuThu)
            For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
                'detail
                query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,SP.TenDonVi,SP.TenQuyCach From ChiTietDonHangBan as CT" & _
                        " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham" & _
                        " Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay='" & Ngay & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
                    detailRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
                    detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
                    detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
                    detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
                    detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
                    detailRow.Item("SoLuong") = FormatNumber(TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham")), True)
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptSanLuongTieuThu
            objReport.Database.Tables("dtSanLuongTieuThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            formatDate2VN(Ngay)
            newRow("Title") = "Ngày " & Ngay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TheoThang()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = "" '"Select * From DonHangBan Order by Ngay_So DESC" REM Where NgayBan='" & NgayBan & "'" & " Order by Ngay_So DESC"
            REM Grouping list
            Dim groupingList As New DataTable
            groupingList = Create_GroupingList()
            'title
            Dim dtLoaiSanPhamTieuThu As New DataTable
            query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
                   " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
                    " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang & "' )"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiSanPhamTieuThu)
            For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
                'detail
                query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,SP.TenDonVi,SP.TenQuyCach From ChiTietDonHangBan as CT" & _
                        " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham" & _
                        " Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Thang='" & Thang & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
                    detailRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
                    detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
                    detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
                    detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
                    detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
                    detailRow.Item("SoLuong") = FormatNumber(TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham")), True)
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptSanLuongTieuThu
            objReport.Database.Tables("dtSanLuongTieuThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Tháng " & Thang
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TheoNam()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim NgayBan As String = dtpTheoNgay.Value.ToShortDateString
            Dim query As String = "" '"Select * From DonHangBan Order by Ngay_So DESC" REM Where NgayBan='" & NgayBan & "'" & " Order by Ngay_So DESC"
            REM Grouping list
            Dim groupingList As New DataTable
            groupingList = Create_GroupingList()
            'title
            Dim dtLoaiSanPhamTieuThu As New DataTable
            query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
                   " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
                    " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam & "' )"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiSanPhamTieuThu)
            For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
                'detail
                query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,SP.TenDonVi,SP.TenQuyCach From ChiTietDonHangBan as CT" & _
                        " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham" & _
                        " Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Nam='" & Nam & "' And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
                    detailRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
                    detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
                    detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
                    detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
                    detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
                    detailRow.Item("SoLuong") = FormatNumber(TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham")), True)
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptSanLuongTieuThu
            objReport.Database.Tables("dtSanLuongTieuThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Năm " & Nam
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TuNgayDenNgay()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            'Dim NgayBan As String = dtpTheoNgay.Value.ToShortDateString
            Dim query As String = "" '"Select * From DonHangBan Order by Ngay_So DESC" REM Where NgayBan='" & NgayBan & "'" & " Order by Ngay_So DESC"
            REM Grouping list
            Dim groupingList As New DataTable
            groupingList = Create_GroupingList()
            'title
            Dim dtLoaiSanPhamTieuThu As New DataTable
            query = "Select Distinct Loai.IDLoai,TenLoai from SanPham as SP" & _
                   " Left Outer Join LoaiSanPham as Loai On SP.IDLoai=Loai.IDLoai" & _
                    " Where IDSanPham In ( Select IDSanPham From ChiTietDonHangBan Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & ")"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtLoaiSanPhamTieuThu)
            For i As Integer = 0 To dtLoaiSanPhamTieuThu.Rows.Count - 1
                'detail
                query = "Select Distinct CT.IDSanPham,SP.MaSanPham,SP.TenSanPham,SP.TenDonVi,SP.TenQuyCach From ChiTietDonHangBan as CT" & _
                        " Left Outer Join SanPham as SP On CT.IDSanPham =  SP.IDSanPham" & _
                        " Where LoaiPhi='HangHoa' And TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " And CT.IDSanPham In ( Select IDSanPham From SanPham Where IDLoai=" & dtLoaiSanPhamTieuThu.Rows(i)("IdLoai") & ")"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("IdLoai") = dtLoaiSanPhamTieuThu.Rows(i)("IdLoai")
                    detailRow.Item("TenLoai") = dtLoaiSanPhamTieuThu.Rows(i)("TenLoai")
                    detailRow.Item("MaSanPham") = dtThongKe.Rows(j)("MaSanPham")
                    detailRow.Item("TenSanPham") = dtThongKe.Rows(j)("TenSanPham")
                    detailRow.Item("DonViTinh") = dtThongKe.Rows(j)("TenDonVi")
                    detailRow.Item("QuyCach") = dtThongKe.Rows(j)("TenQuyCach")
                    detailRow.Item("SoLuong") = FormatNumber(TinhSanLuongTieuThu(dtThongKe.Rows(j)("IDSanPham")), True)
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptSanLuongTieuThu
            objReport.Database.Tables("dtSanLuongTieuThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Từ ngày " & sTuNgay & " Đến ngày " & sDenNgay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
End Class